﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace HustQQServer.DataBase
{
    public class TeamDB
    {
        public static List<string> GetGroupMemberID(string groupID)
        {
            List<string> QQ_IDs = new List<string>();
            string commandString = "SELECT QQ_ID FROM Groups WHERE GroupID = '" + groupID + "'";
            DbManager.Connect();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, DbManager.connectString);
            DbManager.Disconnect();
            DataSet dataSet = new DataSet();
            try
            {
                dataAdapter.Fill(dataSet, "QQ_ID");
                for (int i = 0; i < dataSet.Tables["QQ_ID"].Rows.Count; i++)
                {
                    string qq_ID = (string)dataSet.Tables["QQ_ID"].Rows[i][0];
                    QQ_IDs.Add(qq_ID);
                }

                return QQ_IDs;
            }
            catch
            {
                return null;
            }
        }

        public static string GetGroupName(string groupID)
        {
            string name = null;
            string commandString = "SELECT GroupName FROM Groups WHERE GroupID = '" + groupID + "'";
            DbManager.Connect();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, DbManager.connectString);
            DbManager.Disconnect();
            DataSet dataSet = new DataSet();
            try
            {
                dataAdapter.Fill(dataSet, "GroupName");
                name = (string)dataSet.Tables["GroupName"].Rows[0][0];

                return name;
            }
            catch
            {
                return null;
            }
        }

        public static bool InsertAMember(string groupID, string QQId)
        {
            int item;
            string commandString = "INSERT INTO Groups(GroupID, QQ_ID) " +
                "VALUES('" + groupID + "','" + QQId + "')";
            try
            {

                if (!DbManager.Connect())
                {
                    return false;
                }
                SqlCommand command = new SqlCommand();
                command.Connection = DbManager.MyConnection;
                command.CommandText = commandString;
                item = command.ExecuteNonQuery();
                DbManager.Disconnect();
            }
            catch (SqlException e)
            {
                return false;
            }

            if (item == 1)
                return true;
            else
                return false;
        }


        public static bool DeleteAMember(string groupID, string QQId)
        {
            int item;
            string commandString = "DELETE FROM Groups " +
                "WHERE QQ_ID = '" + QQId + "' AND GroupID = '" + groupID + "'";
            try
            {

                if (!DbManager.Connect())
                {
                    return false;
                }
                SqlCommand command = new SqlCommand();
                command.Connection = DbManager.MyConnection;
                command.CommandText = commandString;
                item = command.ExecuteNonQuery();
                DbManager.Disconnect();
            }
            catch (SqlException e)
            {
                return false;
            }

            if (item == 1)
                return true;
            else
                return false;
        }

        public static List<string> GetAllTeams(string QQ_ID)
        {
            List<string> TeamIds = new List<string>();
            string commandString = "SELECT GroupID FROM Groups WHERE QQ_ID = '" + QQ_ID + "'";
            DbManager.Connect();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, DbManager.connectString);
            DbManager.Disconnect();
            DataSet dataSet = new DataSet();
            try
            {
                dataAdapter.Fill(dataSet, "GroupID");
                for (int i = 0; i < dataSet.Tables["GroupID"].Rows.Count; i++)
                {
                    string qq_ID = (string)dataSet.Tables["GroupID"].Rows[i][0];
                    TeamIds.Add(qq_ID);
                }

                return TeamIds;
            }
            catch
            {
                return null;
            }
        }



        public static string NewTeam(string QQId, string teamName)
        {
            Random ran = new Random(DateTime.Now.Millisecond);
            string groupID = null;


            while (groupID == null)
            {
                groupID = ran.Next(999999).ToString();
                string commandString = "INSERT INTO Groups(GroupID, QQ_ID, GroupName) " +
                "VALUES('" + groupID + "', '" + QQId + "','" + teamName + "')";
                try
                {

                    if (!DbManager.Connect())
                    {
                        return null;
                    }
                    SqlCommand command = new SqlCommand();
                    command.Connection = DbManager.MyConnection;
                    command.CommandText = commandString;
                    if (command.ExecuteNonQuery() != 1)
                    {
                        groupID = null;
                    }
                    DbManager.Disconnect();
                }
                catch (SqlException e)
                {
                    groupID = null;
                }
            }
            return groupID;
        }

    }
}
